package v2ch4.ExecSQL;

import java.io.*;
import java.util.*;
import java.sql.*;
 
class ExecSQL {
   static final String SELECT_BOOKS = "SELECT * FROM books";

   public static void main (String args[]) throws SQLException {



      Connection conn = null;
      Statement  stat = null;
      try
      {
         conn = getConnection();
         //conn.getTransactionIsolation();
         //conn.setAutoCommit(false);
         //conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
         stat = conn.createStatement();
         //executeSql(stat);   //create Table and  Insert date
         stat.executeQuery(SELECT_BOOKS);

      } catch (Exception ex)
      {
         ex.printStackTrace();
      } finally {
          if (stat != null) stat.close();
          if (conn != null) conn.close();
      }
   }

    private static void executeSql(Statement stat)  {

        String cd = new File(".").getAbsolutePath();
        File fileSql = new File(cd+"/sql");
        FilenameFilter only = new OnlyExt("sql");
        File[] suite = fileSql.listFiles(only);
        for (File currentFile : suite) {
            BufferedReader reader = null;

            try {
                reader = new BufferedReader(new FileReader(currentFile));
                String lineSql = null;
                while ((lineSql = reader.readLine()) != null) {
                    try {
                        stat.execute(lineSql);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                  if (reader != null) reader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

   public static Connection getConnection() throws SQLException, IOException, ClassNotFoundException {

      //Class.forName("com.mysql.jdbc.Driver");

      Properties props = PropertyReader.getProps();
      String drivers = props.getProperty("jdbc.drivers");
      if (drivers != null) System.setProperty("jdbc.drivers", drivers);
      String url = props.getProperty("jdbc.url");
      String username = props.getProperty("jdbc.username");
      String password = props.getProperty("jdbc.password");

      return  DriverManager.getConnection(url, username, password);
   }

   public static void showResultSet(Statement stat)  throws SQLException
   { 
      ResultSet result = stat.getResultSet();
      ResultSetMetaData metaData = result.getMetaData();
      int columnCount = metaData.getColumnCount();

      for (int i = 1; i <= columnCount; i++)
      {  
         if (i > 1) System.out.print(", ");
         System.out.print(metaData.getColumnLabel(i));
      }
      System.out.println();

      while (result.next())
      {  
         for (int i = 1; i <= columnCount; i++)
         {  
            if (i > 1) System.out.print(", ");
            System.out.print(result.getString(i));
         }
         System.out.println();
      }
      result.close();
   }


//    try
//    {
//        conn.setAutoCommit (false);
//        Statement s = conn.createStatement ();
//        // передать деньги от одного человека другому
//        s.executeUpdate ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");
//        s.executeUpdate ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");
//        s.close ();
//        conn.commit ();
//        conn.setAutoCommit (true);
//    }
//    catch (SQLException e)
//    {
//        System.err.println ("Transaction failed, rolling back.");
//        Cookbook.printErrorMessage (e);
//        // пустой обработчик исключений, если откат не удался
//        try
//        {
//            conn.rollback ();
//            conn.setAutoCommit (true);
//        }
//        catch (Exception e2) { }
//    }

}
